BigQueryのSQL変換機能を使ってRedshiftとBigqueryの関数の違いを調べてみた

suto

2023.06.05

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

どーも、データアナリティクス事業本部コンサルティングチームのsutoです。

今回はそれに関連してBigQuery Migration ServiceのSQL変換機能を使って、関数の仕様の違いなどでRedshiftのクエリをそのまま使えない場合にどのように変換されるのか・どのように書き換えれば良いかをいくつか例として紹介したいと思います。

BigQuery Migration ServiceのSQL変換機能については、以下の記事が参考になります。

NVL関数

関数内には2つ以上の引数を指定でき、一連の式の中で、Null 以外の最初の式の値を返します
Redshiftで使うことができるNVL関数は、BigQueryでは使えません
- どちらでも使用可能なCOALESCE関数で記述すればOKです
関数内の引数が2つだけの場合、BigQueryではIFNULL関数でも代用可能です
- ※逆にRedshiftではIFNULL関数は使用できません

# Redshift
SELECT
    NVL(f.user_id, t.account_id) AS client_id
FROM
    test_table AS t
    LEFT JOIN test_table2 AS f ON (t.master_id = f.master_id)
;

# BigQuery
SELECT
    coalesce(f.user_id, t.account_id) AS client_id
  FROM
    test_table AS t
    LEFT OUTER JOIN test_table2 AS f ON t.master_id = f.master_id
;

NVL2関数

指定された式の結果が NULL か NOT NULL かに基づいて、2 つの値のいずれかを返します
BigQueryではNVL2関数は使用できないのでCASEを使って条件式に書き直すこととなります

# Redshift
SELECT
    (firstname + ' ' + lastname) as name, 
    NVL2(house_phone, house_phone, mobile_phone) AS contact_info
FROM
    users
;

# BigQuery
SELECT
    concat(users.firstname, ' ', users.lastname) AS name,
    CASE WHEN users.house_phone IS NOT NULL THEN users.house_phone ELSE users.mobile_phone END AS contact_info
  FROM
    users
;

DEOCDE関数

等価条件の結果に応じて、特定の値を別の特定の値またはデフォルト値で置換します
BigQueryではDECODE関数は使用できないのでCASEを使って条件式に書き直すこととなります

# Redshift
SELECT
    decode(user_type, 1, client_id, 0) AS client_id
FROM test_table
;

# BigQuery
SELECT
    CASE
       test_table.user_type
      WHEN 1 THEN test_table.client_id
      ELSE 0
    END AS client_id
  FROM
    test_table
;

注意点

RedshiftのDECODE関数はデフォルト値を指定しない場合NULLを返します
- BigQueryのCASE文の仕様でもELSEがない場合NULLを返す仕様なので問題ありませんが、NULLが格納されることが好ましくないカラムならばデフォルト値を指定しましょう

日付・時刻関数

日付や時刻に関わる関数は表記に違いがあります

# Redshift

# 現在時刻(UTC)
SELECT SYSDATE

# 現在時刻(JST)
SELECT CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE)

# N日前の日付
SELECT TRUNC(DATEADD(DAY, -1, TRUNC(CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE))))

# 2つの日付の差
SELECT DATEDIFF(DAY, '2018-01-01', '2018-01-28')

# BigQuery

# 現在時刻(UTC)
SELECT CURRENT_DATETIME

# 現在時刻(JST)
SELECT CURRENT_DATETIME('Asia/Tokyo')

# N日前の日付
SELECT DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)

# 2つの日付の差
SELECT DATE_DIFF('2018-01-28', '2018-01-01', DAY)

MEDIAN関数

値の範囲の中央値を計算します。範囲の Null 値は無視されます
BigQueryではMEDIAN関数はないため、代わりにPERCENTILE_CONT関数を使って表現できます

# Redshift
SELECT 
    MEDIAN(score) AS score_median
FROM test_table
;

# BigQuery
SELECT 
    PERCENTILE_CONT(score, 0.5) OVER() AS score_median
FROM test_table
limit 1
;

また、BigQuery Migration Serviceで変換してみた際は以下のようなSQLとなりました

# BigQuery
SELECT
    bqutil.fn.cw_array_median(array_agg(test_table.score)) AS score_median
  FROM
    test_table
;

上記はBigQueryのコミュニティ提供のユーザー関数を利用したかたちに変換したようです
- README：

参考ブログ：

一方でAPPROX_QUANTILES関数でも同じ結果が得られそうですが、以下テーブルのようなレコード数が偶数だと違った結果となります

user_id	score
1	3
2	10
3	12
4	30

# BigQuery
SELECT 
    PERCENTILE_CONT(score, 0.5) OVER() AS score_median
FROM test_table
limit 1
;

→ 11.0

# BigQuery
SELECT 
    APPROX_QUANTILES(score, 2)[OFFSET(1)] AS score_median
FROM test_table
limit 1
;

→ 10.0

このように近似集合関数であるAPPROX_QUANTILES関数だと中央の2つのデータの平均を計算してくれないので注意が必要です
- （BigQuery Migration Serviceを使った際、RedshiftのPERCENTILE_CONT関数をAPPROX_QUANTILES関数に変換することがあったため補足として記載）

GREATEST・LEAST関数

任意の数の式のリストから最大値・最小値を返します
この関数は以下のようにRedshiftとBigQueryで返り値が異なります
- Redshift：引数のリスト内にNULLがあった場合、NULLは無視され、NULL以外の値のリストから返り値が決まる
- BigQuery：引数のリスト内にNULLがあった場合、返り値はNULLとなる
- 以前書いたこちらのブログが参考になります

LAG・LEAD関数

パーティションの現在の行より上(前)・下(後)の指定されたオフセットの行の値を返します
どちらでも利用可能な関数でオプションは”offset”が共通しています
- 違いがあるオプションは以下のとおりとなります
- Redshift：IGNORE NULLS（オフセット先の値がNULLの場合スキップ） | RESPECT NULLS（オフセット先の値がNULLの場合そのままNULLを返す）
- BigQuery：default_expression（オフセットのウィンドウフレームに行がない場合に使用される値で、指定しない場合NULLを返す）
問題となるのはRedshift側のオプション”IGNORE NULLS”の動きをBigQueryで表現したい場合です。BigQueryでは「オフセット先の値がNULLの場合、その行をスキップ」する動作を実現するためにはSQL文を工夫する必要があります
LAG関数を例にBigQuery Migration Serviceで変換してみると以下のような結果を得られました

# Redshift

SELECT 
    LAG(t.times) IGNORE NULLS OVER(PARTITION BY t.user_id, t.user_type ORDER BY t.client_id) AS lag_auto_times
FROM test_table AS t

# BigQuery

SELECT
    LAST_VALUE(t.times IGNORE NULLS) OVER (PARTITION BY t.user_id, t.user_type ORDER BY t.client_id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lag_auto_times
  FROM
    test_table AS t
;

LAST_VALUE関数はウィンドウフレームの最後の行に関する式の値を返します
- LAST_VALUE関数はBigQueryでも IGNORE NULLS が使えます
- また、 ORDER BY t.client_id NULLS LASTでNULL値はソート時に最後に配置されます
- OVER句におけるROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDINGは、ウィンドウフレームの範囲を指定します。現在の行の直前の行までの範囲を含みます
- つまり、れぞれのパーティション内で、現在の行の直前の行までの範囲で、times列の最後の非NULL値を返します
ちなみにLEAD関数の場合でも同じく IGNORE NULLS を表現するために以下のように書き換えます

# Redshift

SELECT 
    LEAD(t.times) IGNORE NULLS OVER(PARTITION BY t.user_id, t.user_type ORDER BY t.client_id) AS lag_auto_times
FROM test_table AS t

# BigQuery

SELECT
    FIRST_VALUE(t.times IGNORE NULLS) OVER (PARTITION BY t.user_id, t.user_type ORDER BY t.client_id NULLS LAST ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS lag_auto_times
  FROM
    test_table AS t
;

ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING でウィンドウフレームの範囲として現在の行の次の行から末尾の行までの範囲を指定しています

RATIO_TO_REPORT関数

ウィンドウまたはパーティションの値の合計に対する、ある値の比率を計算する関数で、Redshiftでは標準で使えます
- ただし、BigQueryでは利用できないので純粋に演算で表現する必要があります
- ※このRATIO_TO_REPORT関数についてはBigQuery Migration Serviceではエラーとなって変換できませんでした

# Redshift

SELECT
    sellerid,
    qty,
    RATIO_TO_REPORT(qty) OVER (PARTITION BY sellerid) AS ratio_to_report_qty
FROM
    sales
;

# BigQuery

SELECT
    sellerid,
    qty,
    DIV(qty, SUM(qty)) OVER (PARTITION BY sellerid) AS ratio_to_report_qty
FROM
    sales
;

このようにBigQuery Migration ServiceではだいたいのSQLはうまく変換してくれましたが、たまに変換できなかった関数などもありましたので、とくにウィンドウ関数については各々のドキュメントを比較しながら最終チェックをするのが良さそうです。

BigQueryのSQL変換機能を使ってRedshiftとBigqueryの関数の違いを調べてみた

NVL関数

NVL2関数

DEOCDE関数

日付・時刻関数

MEDIAN関数

GREATEST・LEAST関数

LAG・LEAD関数

RATIO_TO_REPORT関数

関連記事

主なカテゴリ

AWS

おすすめ

プロダクト

コンテンツ

お問い合わせ

運営会社